#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Excel to CSV Converter
将Excel文件转换为CSV文件的工具
"""

import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import os
import csv
from pathlib import Path
import threading
import configparser

try:
    from openpyxl import load_workbook
    OPENPYXL_AVAILABLE = True
except ImportError:
    OPENPYXL_AVAILABLE = False

try:
    import xlrd
    XLRD_AVAILABLE = True
except ImportError:
    XLRD_AVAILABLE = False


class ExcelToCSVConverter:
    def __init__(self, root):
        self.root = root
        self.root.title("Excel to CSV Converter")
        self.root.geometry("600x400")
        self.root.resizable(True, True)
        
        # 设置变量
        self.input_folder = tk.StringVar()
        self.output_folder = tk.StringVar()
        
        # 历史记录
        self.input_history = []
        self.output_history = []
        
        # 配置文件
        self.config_file = "config.ini"
        self.config = configparser.ConfigParser()
        
        # 创建界面
        self.create_widgets()
        
        # 加载配置
        self.load_config()
        
        # 初始化历史记录
        self.init_history()
        
        # 绑定关闭事件
        self.root.protocol("WM_DELETE_WINDOW", self.on_closing)
    
    def create_widgets(self):
        # 主框架
        main_frame = ttk.Frame(self.root, padding="20")
        main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        
        # 配置网格权重
        self.root.columnconfigure(0, weight=1)
        self.root.rowconfigure(0, weight=1)
        main_frame.columnconfigure(1, weight=1)
        
        # 标题
        title_label = ttk.Label(main_frame, text="Excel to CSV Converter", 
                               font=("Arial", 16, "bold"))
        title_label.grid(row=0, column=0, columnspan=3, pady=(0, 20))
        
        # 输入文件夹选择
        ttk.Label(main_frame, text="选择Excel文件夹:").grid(row=1, column=0, sticky=tk.W, pady=5)
        
        # 输入文件夹框架
        input_frame = ttk.Frame(main_frame)
        input_frame.grid(row=1, column=1, sticky=(tk.W, tk.E), padx=(10, 5), pady=5)
        input_frame.columnconfigure(0, weight=1)
        
        self.input_combo = ttk.Combobox(input_frame, textvariable=self.input_folder, width=47)
        self.input_combo.grid(row=0, column=0, sticky=(tk.W, tk.E))
        
        ttk.Button(main_frame, text="浏览", command=self.browse_input_folder).grid(row=1, column=2, padx=(5, 0), pady=5)
        
        # 输出文件夹选择
        ttk.Label(main_frame, text="选择输出文件夹:").grid(row=2, column=0, sticky=tk.W, pady=5)
        
        # 输出文件夹框架
        output_frame = ttk.Frame(main_frame)
        output_frame.grid(row=2, column=1, sticky=(tk.W, tk.E), padx=(10, 5), pady=5)
        output_frame.columnconfigure(0, weight=1)
        
        self.output_combo = ttk.Combobox(output_frame, textvariable=self.output_folder, width=47)
        self.output_combo.grid(row=0, column=0, sticky=(tk.W, tk.E))
        
        ttk.Button(main_frame, text="浏览", command=self.browse_output_folder).grid(row=2, column=2, padx=(5, 0), pady=5)
        
        # 生成按钮
        self.convert_button = ttk.Button(main_frame, text="生成CSV", command=self.start_conversion)
        self.convert_button.grid(row=3, column=0, columnspan=3, pady=20)
        
        # 进度条
        self.progress = ttk.Progressbar(main_frame, mode='indeterminate')
        self.progress.grid(row=4, column=0, columnspan=3, sticky=(tk.W, tk.E), pady=10)
        
        # 状态标签
        self.status_label = ttk.Label(main_frame, text="准备就绪")
        self.status_label.grid(row=5, column=0, columnspan=3, pady=5)
        
        # 日志文本框
        log_frame = ttk.LabelFrame(main_frame, text="转换日志", padding="10")
        log_frame.grid(row=6, column=0, columnspan=3, sticky=(tk.W, tk.E, tk.N, tk.S), pady=10)
        log_frame.columnconfigure(0, weight=1)
        log_frame.rowconfigure(0, weight=1)
        
        self.log_text = tk.Text(log_frame, height=10, width=70)
        scrollbar = ttk.Scrollbar(log_frame, orient="vertical", command=self.log_text.yview)
        self.log_text.configure(yscrollcommand=scrollbar.set)
        
        self.log_text.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        scrollbar.grid(row=0, column=1, sticky=(tk.N, tk.S))
        
        # 配置主框架的行权重
        main_frame.rowconfigure(6, weight=1)
    
    def browse_input_folder(self):
        # 获取当前工作目录作为初始目录
        initial_dir = os.getcwd()
        
        # 如果当前输入框有值且路径存在，则从该路径开始
        current_path = self.input_folder.get()
        if current_path and os.path.exists(current_path):
            initial_dir = current_path
        
        folder = filedialog.askdirectory(
            title="选择包含Excel文件的文件夹",
            initialdir=initial_dir
        )
        if folder:
            # 转换为绝对路径
            abs_folder = os.path.abspath(folder)
            self.input_folder.set(abs_folder)
            # 添加到历史记录
            self.add_to_history(abs_folder, self.input_history, self.input_combo)
            # 保存配置
            self.save_config()
    
    def browse_output_folder(self):
        # 获取当前工作目录作为初始目录
        initial_dir = os.getcwd()
        
        # 如果当前输出框有值且路径存在，则从该路径开始
        current_path = self.output_folder.get()
        if current_path and os.path.exists(current_path):
            initial_dir = current_path
        
        folder = filedialog.askdirectory(
            title="选择CSV文件输出文件夹",
            initialdir=initial_dir
        )
        if folder:
            # 转换为绝对路径
            abs_folder = os.path.abspath(folder)
            self.output_folder.set(abs_folder)
            # 添加到历史记录
            self.add_to_history(abs_folder, self.output_history, self.output_combo)
            # 保存配置
            self.save_config()
    
    def add_to_history(self, folder, history_list, combo_widget):
        """添加文件夹到历史记录"""
        if folder not in history_list:
            history_list.insert(0, folder)
            # 限制历史记录数量
            if len(history_list) > 10:
                history_list.pop()
        else:
            # 如果已存在，移到最前面
            history_list.remove(folder)
            history_list.insert(0, folder)
        
        # 更新下拉框选项
        combo_widget['values'] = history_list
    
    def init_history(self):
        """初始化历史记录"""
        # 添加一些常用的默认路径（使用绝对路径）
        default_input_paths = [
            os.path.abspath("data"),
            os.path.join(os.getcwd(), "data"),
            os.path.join(os.getcwd(), "input"),
            os.path.join(os.getcwd(), "excel"),
            os.path.join(os.getcwd(), "files")
        ]
        
        default_output_paths = [
            os.path.abspath("output"),
            os.path.join(os.getcwd(), "output"),
            os.path.join(os.getcwd(), "result"),
            os.path.join(os.getcwd(), "csv"),
            os.path.join(os.getcwd(), "converted")
        ]
        
        # 只添加存在的路径
        for path in default_input_paths:
            if os.path.exists(path):
                self.input_history.append(path)
        
        for path in default_output_paths:
            if os.path.exists(path):
                self.output_history.append(path)
        
        # 更新下拉框
        self.input_combo['values'] = self.input_history
        self.output_combo['values'] = self.output_history
    
    def load_config(self):
        """加载配置文件"""
        try:
            if os.path.exists(self.config_file):
                self.config.read(self.config_file, encoding='utf-8')
                
                # 加载输入文件夹路径
                if self.config.has_option('paths', 'input_folder'):
                    input_path = self.config.get('paths', 'input_folder')
                    if os.path.exists(input_path):
                        self.input_folder.set(input_path)
                    else:
                        self.input_folder.set(os.path.abspath("data"))
                else:
                    self.input_folder.set(os.path.abspath("data"))
                
                # 加载输出文件夹路径
                if self.config.has_option('paths', 'output_folder'):
                    output_path = self.config.get('paths', 'output_folder')
                    if os.path.exists(output_path):
                        self.output_folder.set(output_path)
                    else:
                        self.output_folder.set(os.path.abspath("output"))
                else:
                    self.output_folder.set(os.path.abspath("output"))
                
                # 加载历史记录
                if self.config.has_option('history', 'input_history'):
                    input_history_str = self.config.get('history', 'input_history')
                    if input_history_str:
                        self.input_history = [path.strip() for path in input_history_str.split('|') if path.strip()]
                
                if self.config.has_option('history', 'output_history'):
                    output_history_str = self.config.get('history', 'output_history')
                    if output_history_str:
                        self.output_history = [path.strip() for path in output_history_str.split('|') if path.strip()]
            else:
                # 如果配置文件不存在，设置默认值
                self.input_folder.set(os.path.abspath("data"))
                self.output_folder.set(os.path.abspath("output"))
        except Exception as e:
            print("加载配置文件失败: {}".format(e))
            # 设置默认值
            self.input_folder.set(os.path.abspath("data"))
            self.output_folder.set(os.path.abspath("output"))
    
    def save_config(self):
        """保存配置文件"""
        try:
            # 确保配置节存在
            if not self.config.has_section('paths'):
                self.config.add_section('paths')
            if not self.config.has_section('history'):
                self.config.add_section('history')
            
            # 保存当前路径
            self.config.set('paths', 'input_folder', self.input_folder.get())
            self.config.set('paths', 'output_folder', self.output_folder.get())
            
            # 保存历史记录
            self.config.set('history', 'input_history', '|'.join(self.input_history))
            self.config.set('history', 'output_history', '|'.join(self.output_history))
            
            # 写入文件
            with open(self.config_file, 'w', encoding='utf-8') as f:
                self.config.write(f)
        except Exception as e:
            print("保存配置文件失败: {}".format(e))
    
    def on_closing(self):
        """程序关闭时的处理"""
        # 保存当前配置
        self.save_config()
        # 关闭程序
        self.root.destroy()
    
    def log_message(self, message):
        """添加日志消息"""
        self.log_text.insert(tk.END, "{}\n".format(message))
        self.log_text.see(tk.END)
        self.root.update_idletasks()
    
    def start_conversion(self):
        """开始转换过程"""
        input_path = self.input_folder.get()
        output_path = self.output_folder.get()
        
        # 验证输入
        if not input_path or not output_path:
            messagebox.showerror("错误", "请选择输入和输出文件夹")
            return
        
        if not os.path.exists(input_path):
            messagebox.showerror("错误", "输入文件夹不存在: {}".format(input_path))
            return
        
        # 创建输出文件夹
        try:
            os.makedirs(output_path, exist_ok=True)
        except Exception as e:
            messagebox.showerror("错误", "无法创建输出文件夹: {}".format(e))
            return
        
        # 禁用按钮并开始转换
        self.convert_button.config(state='disabled')
        self.progress.start()
        self.status_label.config(text="正在转换...")
        self.log_text.delete(1.0, tk.END)
        
        # 在新线程中执行转换
        thread = threading.Thread(target=self.convert_excel_files, args=(input_path, output_path))
        thread.daemon = True
        thread.start()
    
    def convert_excel_files(self, input_path, output_path):
        """转换Excel文件为CSV"""
        try:
            # 检查依赖包
            if not OPENPYXL_AVAILABLE and not XLRD_AVAILABLE:
                self.root.after(0, lambda: self.log_message("错误: 缺少必要的依赖包"))
                self.root.after(0, lambda: self.log_message("请安装: pip install openpyxl xlrd"))
                self.root.after(0, self.conversion_complete)
                return
            
            # 查找所有Excel文件
            excel_files = []
            for ext in ['*.xlsx', '*.xls']:
                excel_files.extend(Path(input_path).glob(ext))
            
            if not excel_files:
                self.root.after(0, lambda: self.log_message("未找到Excel文件"))
                self.root.after(0, self.conversion_complete)
                return
            
            self.root.after(0, lambda: self.log_message("找到 {} 个Excel文件".format(len(excel_files))))
            
            success_count = 0
            error_count = 0
            
            for excel_file in excel_files:
                try:
                    self.root.after(0, lambda f=excel_file: self.log_message("正在处理: {}".format(f.name)))
                    
                    # 根据文件扩展名选择处理方法
                    if excel_file.suffix.lower() == '.xlsx':
                        if OPENPYXL_AVAILABLE:
                            self.convert_xlsx_to_csv(excel_file, output_path)
                        else:
                            self.root.after(0, lambda f=excel_file.name: self.log_message("✗ 无法处理 {}: 需要安装 openpyxl".format(f)))
                            error_count += 1
                            continue
                    elif excel_file.suffix.lower() == '.xls':
                        if XLRD_AVAILABLE:
                            self.convert_xls_to_csv(excel_file, output_path)
                        else:
                            self.root.after(0, lambda f=excel_file.name: self.log_message("✗ 无法处理 {}: 需要安装 xlrd".format(f)))
                            error_count += 1
                            continue
                    
                    self.root.after(0, lambda f=excel_file.stem: self.log_message("✓ 成功生成: {}.csv".format(f)))
                    success_count += 1
                    
                except Exception as e:
                    self.root.after(0, lambda f=excel_file.name, e=e: self.log_message("✗ 处理失败 {}: {}".format(f, str(e))))
                    error_count += 1
            
            # 转换完成
            self.root.after(0, lambda: self.log_message("\n转换完成! 成功: {}, 失败: {}".format(success_count, error_count)))
            
        except Exception as e:
            self.root.after(0, lambda: self.log_message("转换过程中发生错误: {}".format(str(e))))
        
        finally:
            self.root.after(0, self.conversion_complete)
    
    def convert_xlsx_to_csv(self, excel_file, output_path):
        """转换.xlsx文件为CSV"""
        workbook = load_workbook(excel_file, data_only=True)
        worksheet = workbook.active  # 获取第一个工作表
        
        csv_filename = excel_file.stem + '.csv'
        csv_path = os.path.join(output_path, csv_filename)
        
        with open(csv_path, 'w', newline='', encoding='utf-8-sig') as csvfile:
            writer = csv.writer(csvfile)
            
            for row in worksheet.iter_rows(values_only=True):
                writer.writerow(row)
    
    def convert_xls_to_csv(self, excel_file, output_path):
        """转换.xls文件为CSV"""
        workbook = xlrd.open_workbook(excel_file)
        worksheet = workbook.sheet_by_index(0)  # 获取第一个工作表
        
        csv_filename = excel_file.stem + '.csv'
        csv_path = os.path.join(output_path, csv_filename)
        
        with open(csv_path, 'w', newline='', encoding='utf-8-sig') as csvfile:
            writer = csv.writer(csvfile)
            
            for row_num in range(worksheet.nrows):
                row = worksheet.row_values(row_num)
                writer.writerow(row)
    
    def conversion_complete(self):
        """转换完成后的清理工作"""
        self.progress.stop()
        self.convert_button.config(state='normal')
        self.status_label.config(text="转换完成")


def main():
    root = tk.Tk()
    app = ExcelToCSVConverter(root)
    root.mainloop()


if __name__ == "__main__":
    main()
